<?php
/**
 * Description
 * User: duanwenjie
 * Date: 2019/5/9
 * Time: 11:59 AM
 */

namespace app\index\service;
use app\common\ConfigBase;
use app\common\model\ToolModel;
use app\common\service\DownService;
use think\Db;
use think\Exception;

class ShopwarehouseinService
{
    static public function getList($param, $is_download = false)
    {
        try{
            $where = "where 1 = 1 ";
            $pageNumber = isset($param['page']) ? $param['page'] : 1;
            $pageData = isset($param['limit']) ? $param['limit'] : 20;
            $warehouse_shop_permission = ConfigBase::getWarehouseShopPermission();  // 仓库权限
            $id = isset($param['id']) ? $param['id'] : '';  // ID
            $base_warehouse_id = isset($param['base_warehouse_id']) ? $param['base_warehouse_id'] : '';  // 收货仓库ID
            $business_sn = isset($param['business_sn']) ? $param['business_sn'] : '';  // 入库业务单号
            $batch = isset($param['batch']) ? $param['batch'] : '';  // 入库批次号
            $type = isset($param['type']) ? $param['type'] : '';  // 入库类型
            $status = isset($param['status']) ? $param['status'] : '';  // 状态
            $offset = ($pageNumber - 1) * $pageData;
            $limit = " LIMIT $offset,$pageData";

            if (!empty($id)) {
                $id = trim($id);
                $where .= " AND id = $id";
            }
            if (!empty($base_warehouse_id)) {
                $base_warehouse_id = trim($base_warehouse_id);
                $where .= " AND base_warehouse_id = $base_warehouse_id";
            }
            if (!empty($type)) {
                $type = trim($type);
                $where .= " AND type = $type";
            }
            if (!empty($business_sn)) {
                $business_sn = trim($business_sn);
                $where .= " AND business_sn = '{$business_sn}'";
            }
            if (!empty($batch)) {
                $batch = trim($batch);
                $where .= " AND batch = '{$batch}'";
            }
            if (!empty($status)) {
                $where .= " AND status = {$status}";
            }
            if (!empty($warehouse_shop_permission)) {
                $warehouse_shop_permission = trim($warehouse_shop_permission);
                $where .= " AND base_warehouse_id in ($warehouse_shop_permission)";
            }else{
                $where .= " AND base_warehouse_id in ('')";
            }

            $sqlCount = "SELECT COUNT(id) num FROM shop_warehouse_in {$where}";
            $countArr = ToolModel::queryData($sqlCount);
            if (empty($countArr) || $countArr[0]['num'] == 0) {
                if ($is_download) exitJson(["code" => "500", "msg" => "无有效数据"]);
                return array('total' => 0, 'list' => array());
            }
            $fileds = 'id,base_warehouse_id,base_warehouse_name,batch,business_sn,sku_type,
                      operator,update_time,create_time,type,status,case type when 0 then "新建" 
                      when 1 then "调拨入库" when 2 then "采购入库" when 3 then "盘盈入库" end type_name,
                      case status when 1 then "已确定" when -1 then "未确定" end status_name';

            $sql = "SELECT {$fileds} FROM shop_warehouse_in {$where} ORDER BY id DESC {$limit}";
            if ($is_download) return "SELECT {$fileds} FROM shop_warehouse_in {$where} ORDER BY id DESC ";

            $list = ToolModel::queryData($sql);
            $data = array();
            $data['total'] = intval($countArr[0]['num']);
            $data['list'] = $list;

            return $data;
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }



    /**
     * 导出数据
     * @param $param
     * @return array
     * @author duanwenjie
     */
    static public function export($param)
    {
        $sql = self::getList($param, true);
        $title = array(
            'base_warehouse_name' => '收货仓库',
            'batch' => '入库批次号',
            'business_sn' => '入库业务单号',
            'sku_type' => 'SKU种类',
            'type_name' => '入库类型',
            'operator' => '修改人',
            'update_time' => '修改时间',
            'status_name' => '状态'
        );
        $res = DownService::getData($sql, $title, '', $param);

        return $res;
    }



    /**
     * 获取店仓入库明细
     * @param $param
     * @return false|\PDOStatement|string|\think\Collection
     * @author duanwenjie
     */
    static public function getShopwarehouseinDetail($param)
    {
        $id = $param['id'];
        $res = self::getList($param);
        $res_detail = Db::name('shop_warehouse_in_detail')->field('*')->where('shop_warehouse_in_id','=',$id)->select();
        foreach ($res_detail as &$value){
            $value['original_quantity_in'] = $value['quantity_in'];
            $value['original_remark'] = $value['remark'];
        }
        $data = array(
            "info" => $res['list'],
            "detail" => $res_detail
        );

        return $data;
    }


    /**
     * 编辑店仓入库明细详情
     * 确认入库执行插入数据到店仓成本
     * @param $param
     * @throws Exception
     * @author duanwenjie
     */
    static public function edit($param)
    {
        try{
            /*实时编辑保存*/
            $is_save = isset($param['is_save']) ? true : false;
            $tijiao_type = isset($param['tijiao_type']) ? $param['tijiao_type'] : '';
            $status = '';
            if ($is_save){
                unset($param['is_save']);
                unset($param['original_quantity_in']);
                unset($param['original_remark']);
                ToolModel::dbInsertOrUpdate('shop_warehouse_in_detail', array($param));
                returnJson('1', '操作成功');
            }
            // 获取当前操作数据的状态
            $status = Db::name('shop_warehouse_in')->where('id', '=', $param['id'])->value('status');

            // 确定入库,数据状态为未确定,才走店仓成本更新、库存管理更新操作
            // 出库状态 状态 -1：未确定 1：已确定 （只有是未确定的数据才允许入库处理）
            if ($status == -1){
                //== 店仓入库操作 ==
                //加文件锁避免多人同时操作
                ToolModel::startTrans();
                try{
                    $file = FILE_PATH . "shopwarehousein_lock.txt";
                    $fp = fopen($file, 'w');
                    if ($fp === false){
                        ToolModel::rollback();
                        returnJson('500', '系统繁忙,操作失败!');
                    }
                    if (flock($fp, LOCK_EX | LOCK_NB)){
                        $base_warehouse_id = isset($param['base_warehouse_id']) ? $param['base_warehouse_id'] : "";
                        $base_warehouse_name = isset($param['base_warehouse_name']) ? $param['base_warehouse_name'] : "";
                        $batch = isset($param['batch']) ? $param['batch'] : "";
                        $business_sn = isset($param['business_sn']) ? $param['business_sn'] : ""; // 入库业务单号
                        $type = isset($param['type']) ? $param['type'] : ""; // 入库类型 1：调拨入库 2：采购入库 3：盘盈入库
                        $allocate_order_id = Db::name('allocate_order')->where('allocate_order_sn', '=', $business_sn)->value('id'); // 调拨单id
                        $query = Db::name('allocate_order_detail');
                        $list = isset($param['list']) ? json_decode($param['list'], true) : array();
                        $update_data = array();
                        $update_detail_data = array();
                        $update_shop_warehouse_cost_data = array();
                        $update_shop_warehouse_stock_data = array();
                        if (!empty($list)){
                            $skus = array_unique(array_column($list, 'sku'));
                            $sku_status = self::getSkuStatus($skus, $base_warehouse_id);
                            foreach ($list as $v){
                                /*更新明细*/
                                $update_detail_data[] = array(
                                    "id" => $v['id'],
                                    "quantity_in" => $v['quantity_in'],
                                    "remark" => $v['remark']
                                );
                                $update_shop_warehouse_cost_data[] = array(
                                    "base_warehouse_id" => $base_warehouse_id,
                                    "base_warehouse_name" => $base_warehouse_name,
                                    "batch" => $batch,
                                    "business_sn_in" => $business_sn, // 增加库存成本的入库业务单号
                                    "sku" => $v['sku'],
                                    "sku_name" => $v['sku_name'],
                                    "quantity" => $v['quantity_in'], // 总入库数量 => 入库数量
                                    "surplus_quantity" => $v['quantity_in'], // 批次结余数量 => 入库数量
                                    "price" => $v['price']);
                                $update_shop_warehouse_stock_data[] = array(
                                    "base_warehouse_id" => $base_warehouse_id,
                                    "base_warehouse_name" => $base_warehouse_name,
                                    "sku" => $v['sku'],
                                    "sku_name" => $v['sku_name'],
                                    "sku_status" => isset($sku_status[$v['sku']]) ? $sku_status[$v['sku']]['status'] : 1, // 如果没有SKU状态的一律赋值为新产品
                                );
                                /*如果是type = 1，调拨单入库则更新调拨单明细实际入库数量*/
                                if ($type == 1){
                                    $query->where('allocate_order_id', '=', $allocate_order_id)->where('sku', '=', $v['sku'])->update(array("quantity" => $v['quantity_in'])); // 实际入库数量 => 入库数量
                                }
                            }
                            $operator =  CN_NAME ? CN_NAME : 'admin';
                            $update_data[] = array("id" => $list[0]['shop_warehouse_in_id'], "status" => 1, "operator" => $operator); // 更新数据状态为已确定
                        }

                        if ($tijiao_type == 1) ToolModel::dbInsertOrUpdate('allocate_order', array(["id" => $allocate_order_id, "status" => 1])); // 更新调拨单主表状态为1：已完成
                        if (!empty($update_detail_data)){
                            $res_detail = ToolModel::dbInsertOrUpdate('shop_warehouse_in_detail', $update_detail_data); // 更新明细
                            if (!empty($update_data)) ToolModel::dbInsertOrUpdate('shop_warehouse_in', $update_data); // 更新店仓入库
                            if (!empty($update_shop_warehouse_cost_data)) ToolModel::dbInsertOrUpdate('shop_warehouse_cost', $update_shop_warehouse_cost_data); // 插入数据到店仓成本
                            if (!empty($update_shop_warehouse_stock_data)) ToolModel::dbInsertOrUpdate('shop_warehouse_stock', $update_shop_warehouse_stock_data); // 插入数据到店仓库存

                            // 插入完店仓库存数据后，再更新一次店仓库存管理的实物库存、库存成本、加权单价、库龄数据
                            if (!empty($list)){
                                $skus_ = array_unique(array_column($list, 'sku'));
                                $sku_infos = self::getAllSkuStockInfos($skus_, $base_warehouse_id);
                                $update_shop_warehouse_stock_data_ = array_values($sku_infos);
                                if (!empty($sku_infos)) ToolModel::dbInsertOrUpdate('shop_warehouse_stock', $update_shop_warehouse_stock_data_);
                            }
                            ToolModel::commit();
                            ToolModel::closeFp($fp);
                            returnJson('1', '操作成功');
                        }else{
                            ToolModel::rollback();
                            fclose($fp);
                            returnJson('500', '更新数据为空');
                        }
                    }else{
                        ToolModel::rollback();
                        fclose($fp);
                        returnJson('500', '系统繁忙,请刷新页面后再操作!');
                    }
                }catch (\Exception $e){
                    ToolModel::rollback();
                    trace($e->getMessage(), 'error');
                    !empty($fp) && fclose($fp);
                    returnJson('600', '失败' . $e->getMessage());
                }
            }else{
                returnJson('500', '操作数据状态有误');
            }
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            returnJson('500', $e->getMessage());
        }

    }


    /**
     * 批量获取SKU状态
     * @param $skus
     * @param $base_warehouse_id
     * @return false|\PDOStatement|string|\think\Collection
     * @author duanwenjie
     */
    static public function getSkuStatus($skus, $base_warehouse_id)
    {
        $res = Db::name('store_sku')->alias('SS')
            ->join('base_shop BS','SS.shop = BS.shop_name','left')
            ->join('base_warehouse BW','BS.id = BW.base_shop_id','left')
            ->where('SS.sku','in',$skus)
            ->where('BW.id','=',$base_warehouse_id)
            ->column('SS.status,SS.cycle_stage,SS.sku_grade,SS.shop','SS.sku');

        return $res;
    }


    /**
     * 批量获取SKU的实物库存、库存成本、加权单价、库龄
     * @param $skus
     * @param $base_warehouse_id
     * @return array
     * @throws Exception
     * @author duanwenjie
     */
    static public function getAllSkuStockInfos($skus, $base_warehouse_id)
    {
       try{
           /*获取 实物库存、库存成本、加权单价*/
           $res = Db::name('shop_warehouse_cost')
               ->where('sku','in',$skus)
               ->where('base_warehouse_id','=',$base_warehouse_id)
               ->group('sku')
               ->column('sku,base_warehouse_id,SUM(surplus_quantity) as physical_stock,SUM(surplus_quantity * price) as cost,
            (SUM(surplus_quantity * price)/SUM(quantity)) as wam','sku');

           /*获取 库龄*/
           $exist_sku = array();
           $sku_stock_age = array();
           if (!empty($res)){
               $res2 = Db::name('shop_warehouse_cost')
                   ->where('sku', 'in', $skus)
                   ->where('base_warehouse_id', '=', $base_warehouse_id)
                   ->select();

               if (!empty($res2)){
                   foreach ($res2 as $value){
                       /*实物库存 => 成本管理中当前店仓下SKU所有的批次结余总和*/
                       $physical_stock = isset($res[$value['sku']]) ? $res[$value['sku']]['physical_stock'] : 0;
                       if ($physical_stock == 0){
                           $sku_stock_age[$value['sku']] = 0; // 如果没有库存记录，则库龄为0
                           continue;
                       }

                       /*库龄等于(每批次结余数量/实物库存)乘以(当前时间-入库时间)的总和*/
                       $stock_age = round(($value['surplus_quantity'] / $physical_stock) * ((time() - strtotime($value['create_time'])) / 86400), 2); // 库龄保留两位小数
                       if (in_array($value['sku'], $exist_sku)){
                           $sku_stock_age[$value['sku']] += $stock_age;
                       }else{
                           $sku_stock_age[$value['sku']] = $stock_age;
                           $exist_sku[] = $value['sku'];
                       }
                   }
                   foreach ($res as $k => &$val){
                       $physical_stock = isset($res[$val['sku']]) ? $res[$val['sku']]['physical_stock'] : 0;
                       if ($physical_stock == 0){
                           $val['wam'] = 0; // 如果没有库存记录，则库存加权单价为0
                           continue;
                       }
                       $val['stock_age'] = isset($sku_stock_age[$k]) ? $sku_stock_age[$k] : 0;
                       $val['wam'] = $val['cost'] / $val['physical_stock']; // 计算库存加权单价
                   }
               }
           }

           return $res;
       }catch (Exception $e){
           trace($e->getMessage(), 'error');
           throw new Exception($e->getMessage());
       }
    }

}